package com.mbc.common.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;


import com.mbc.common.entities.ApplicationItem;

public class ApplicationItemDAOImpls extends DAO<ApplicationItem> implements ApplicationItemDAO {

	public ApplicationItemDAOImpls(Connection conn)
	{
		this.conn = conn;
		this.table_name = "ApplicationItem";
	}

	@Override
	public void create(ApplicationItem entity) throws SQLException {
		query = "insert into " + table_name + " ( appitemid, code, name, parentid, menuorder, accesscodes) values( ?, ?, ?, ?, ?, ?)";
		pstmt = conn.prepareStatement(query);
		int i = 1;
		pstmt.setLong(i++, entity.getAppItemId());
		pstmt.setString(i++, entity.getCode());
		pstmt.setString(i++, entity.getName());
		pstmt.setLong(i++, entity.getParentId());
		pstmt.setInt(i++, entity.getMenuOrder());
		pstmt.setString(i++, entity.getAccessCodes());
		pstmt.executeUpdate();
		pstmt.close();
	}

	@Override
	public void update(ApplicationItem entity) throws SQLException {
		query = "update " + table_name + " set  code = ?, name = ?, parentid = ?, menuorder = ?, accesscodes = ? " +
				"where appitemid = " + entity.getAppItemId();
		pstmt = conn.prepareStatement(query);
		int i = 1;
		pstmt.setString(i++, entity.getCode());
		pstmt.setString(i++, entity.getName());
		pstmt.setLong(i++, entity.getParentId());
		pstmt.setInt(i++, entity.getMenuOrder());
		pstmt.setString(i++, entity.getAccessCodes());
		pstmt.executeUpdate();
		pstmt.close();
	}

	@Override
	public void delete(long key) throws SQLException {
		query = getDeleteQuery() + " where appitemid = " + key;
		pstmt = conn.prepareStatement(query);
		pstmt.executeUpdate();
		pstmt.close();
	}

	@Override
	public ApplicationItem find(long key) throws SQLException {
		ApplicationItem l_ApplicationItem = null;
		query = getSelectQuery() + " where appitemid = " + key;
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		if(rs.next())
			l_ApplicationItem = readRecord(rs);
		rs.close();
		pstmt.close();
		return l_ApplicationItem;
	}

	@Override
	public ArrayList<ApplicationItem> findAll() throws SQLException {
		ArrayList<ApplicationItem> l_ApplicationItemList = new ArrayList<ApplicationItem>();
		query = getSelectQuery();
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		while (rs.next()) {
			l_ApplicationItemList.add(readRecord(rs));
		}
		rs.close();
		pstmt.close();
		return l_ApplicationItemList;
	}

	@Override
	protected ApplicationItem readRecord(ResultSet rs) throws SQLException {
		ApplicationItem l_entity = new ApplicationItem();
				l_entity.setAppItemId(rs.getLong("appitemid"));
		l_entity.setCode(rs.getString("code"));
		l_entity.setName(rs.getString("name"));
		l_entity.setParentId(rs.getLong("parentid"));
		l_entity.setMenuOrder(rs.getInt("menuorder"));
		l_entity.setAccessCodes(rs.getString("AccessCodes"));
		return l_entity;
	}

	@Override
	public ArrayList<ApplicationItem> findByUserId(long userId)
			throws SQLException {
		ArrayList<ApplicationItem> l_AppItemList = new ArrayList<ApplicationItem>();
		query = "select distinct(d.appitemid), d.code, d.[name], d.parentid, d.menuorder, c.accesscodes from [user] as a" +
			" inner join userinroles as b on a.userid = b.userid" + 
			" inner join levelapplicationitem as c on c.roleid = b.roleid" + 
			" inner join applicationitem as d on c.appitemid = d.appitemid" + 
			" where a.userid = " + userId + 
			" group by d.appitemid, d.code, d.[name], d.parentid, d.menuorder, c.AccessCodes" + 
			" order by d.menuorder";
		pstmt = conn.prepareStatement(query);
		rs = pstmt.executeQuery();
		while (rs.next()) {
			l_AppItemList.add(readRecord(rs));
		}
		rs.close();
		pstmt.close();
		return l_AppItemList;
	}
}
